/**
 * 
 */
package metier.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import metier.ElementListe;
import metier.Enseignant;
import metier.ExceptionMetier;
import metier.Groupe;
import metier.See;
import metier.Service;
import metier.TypeHeure;

/**
 * @author Malena Agip
 *
 */
public class ServiceJDBC extends Service {

    /* Attributs prives */
    private final static String ID = "id_service"; //Nom de l'attribut service dans la BD
    private final static String GROUPE = "GROUPEETUDIANT";
    private final static String SEE = "SEE";
    private final static String ENSEIGNANT = "ENSEIGNANT";
    private final static String TYPEHEURE = "TYPEHEURE";
    private final static String NOMTABLE = "Service";
    //Requete SQL permettant de mettre a jour l'objet dans la base de donnees
    private final static String updateQuery = "update "
            + NOMTABLE + " set "
            + GROUPE + " = ?, "
            + SEE + " = ?, "
            + ENSEIGNANT + " = ?, "
            + TYPEHEURE + " = ? where "
            + ID + " = ?";
    private final static String selectQuery = "select * from "
            + NOMTABLE;
    private final static String selectQuerySee = "SELECT * from "
            +NOMTABLE + " WHERE "
            +SEE + "=?";

    private final static String selectQueryMatiere = "SELECT serv.* from service serv, see s" 
    		+ " WHERE serv.see = s.id_see AND s.matiere = ?";

    private final static String selectQueryConsulte = "SELECT serv.* from service serv,see se,matiere ma,ue u,semestre sem" +
    		" where serv"+"."+SEE+" =se.id_see and se.matiere=ma.id_matiere" +
	" and ma.ue=u.id_ue and u.semestre=sem.id_semestre  ";
    
    private final static String loadQuery = "select * from "
            + NOMTABLE + " where "
            + ID + " = ?";
    private final static String deleteQuery = "delete from "
            + NOMTABLE + " where "
            + ID + "= ?";
    private final static String idQuery = "SELECT seq_service.nextval  FROM dual";
    private final static String insertQuery = "insert into " + NOMTABLE + " values(?, ?, ?, ?, ?)";

    public ServiceJDBC(See see, Enseignant enseignant,
            TypeHeure typeheure, Groupe groupe) {
        super(see, enseignant, typeheure, groupe);
    }

    public ServiceJDBC(){
        super();
    }

    @Override
    public void delete() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement sta = con.prepareStatement(deleteQuery);
            sta.setInt(1, getId());
            sta.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Delete failed, ClassNotFound", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Delete failed, SQLexception", ex);
        }
    }

    @Override
    public void insert() throws ExceptionMetier {
         try {
             System.out.println(getId());
            Connection con = ConnectionJDBC.getConnection();
			PreparedStatement psta = con.prepareStatement(insertQuery);
            if(getId() == 0) setId(createId());
			psta.setInt(1, this.getId());
            psta.setInt(2,getGroupe().getId());
            psta.setInt(3,getTypeheure().getId());
            psta.setInt(4,getSee().getId());
            psta.setInt(5,getEnseignant().getId());
			psta.executeQuery();
         } catch (ClassNotFoundException ex) {
             throw new ExceptionMetier("Insert failed, ClassNotFound", ex);
         } catch (SQLException ex) {
             throw new ExceptionMetier("Insert failed, SQLexception", ex);
        }
    }

    /**
     * @param idAnneeEtude
     * @param listeIdEnseignant
     * @param listeIdUE
     * @return liste des annees d'etude correspondants a la recherche
     */
    public ArrayList<Service> creerListeService(int idAnneeEtude, int listeIdEnseignant, int listeIdUE) {
        // TODO
        return null;
    }

    @Override
    public void update() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement updateService = con.prepareStatement(updateQuery);
            updateService.setInt(1, getGroupe().getId());
            updateService.setInt(2, getSee().getId());
            updateService.setInt(3, getEnseignant().getId());
            updateService.setInt(4, getTypeheure().getId());
            updateService.setInt(5, getId());
            updateService.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Update failed, ClassNotFound", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Update failed, SQLException", ex);
        }
    }

    protected int createId() throws ExceptionMetier{
	    try {
	        Connection con = ConnectionJDBC.getConnection();
	        Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	        ResultSet rset = sta.executeQuery(idQuery);
	        if (!rset.next())
	            throw new ExceptionMetier("creating new id failed due to non generated id");
	        int newId = rset.getInt(1);
	        rset.close();
	        sta.close();
	        return newId;	        
	        }
	         catch (ClassNotFoundException ex) {
	        throw new ExceptionMetier("creating new id failed", ex);
	    } catch (SQLException ex) {
	        throw new ExceptionMetier("creating new id failed", ex);
	    }
    }


    @Override
    public void load(int id) throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement loadService = con.prepareStatement(loadQuery);
            loadService.setInt(1, id);
            ResultSet res = loadService.executeQuery();
            if (!res.next()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            if (res.getInt(ID) != id) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            setId(id);
            Groupe gp = new GroupeJDBC();
            TypeHeure tph = new TypeHeureJDBC();
            Enseignant ens = new EnseignantJDBC();
            See se = new SeeJDBC();
            gp.load(res.getInt(GROUPE));
            tph.load(res.getInt(TYPEHEURE));
            ens.load(res.getInt(ENSEIGNANT));
            se.load(res.getInt(SEE));
            setGroupe(gp);
            setTypeheure(tph);
            setEnseignant(ens);
            setSee(se);
            res.close();
            loadService.close();


        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Load failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Load failed", ex);
        }
    }

    public static ArrayList<ElementListe> select() throws ExceptionMetier {
        ArrayList<ElementListe> listeService = new ArrayList<ElementListe>();
        try {
            ResultSet res = null;
            Connection con = ConnectionJDBC.getConnection();
            Statement s = con.createStatement();
            res = s.executeQuery(selectQuery);

            // Ajoute les services une par une a la liste
            while (res.next()) {
                //r�cup�re le service
                Service service = new ServiceJDBC();
                service.load(res.getInt(ID));

                listeService.add(new ElementListe(service.getId(), service.toString()));
            }
            return listeService;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }

    public static ArrayList<ElementListe> select(int idAnneeEtude,
			int listeEnseignantsFiltre, int listeUEsFiltre) throws ExceptionMetier {
        ArrayList<ElementListe> listeService = new ArrayList<ElementListe>();
       String temp=selectQueryConsulte;
        try {

            Connection con = ConnectionJDBC.getConnection();
            // -1pour indiquer qu'il n'est pas un parametre 
            
           
            if(idAnneeEtude!=-1)
            {
            	temp=temp + "sem.anneeetude="+idAnneeEtude +" and"; 
            	
            }
            if(listeEnseignantsFiltre!=-1)
            { 
            	temp=temp+"serv.enseignant="+listeEnseignantsFiltre+" and";
            	
            	
            }
            if(listeUEsFiltre!=-1)
            {
            	temp=temp+"u.ue="+listeUEsFiltre;

            	
            }
           temp=temp+" 1=1";
            
           System.out.print(temp);
           
            PreparedStatement selectservice = con.prepareStatement(selectQueryConsulte);
            ResultSet res = selectservice.executeQuery();
            // Ajoute les service une par une a la liste selon les parametres ingresees
            while (res.next()) {

                //r�cup�re le service
                Service service = new ServiceJDBC();
                service.load(res.getInt(ID));

                listeService.add(new ElementListe(service.getId(), service.toString()));


            }
            return listeService;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }
    
    public static ArrayList<ElementListe> selectMatiere(int matiere) throws ExceptionMetier {
        ArrayList<ElementListe> listeService = new ArrayList<ElementListe>();
        try {
        	 Connection con = ConnectionJDBC.getConnection();
             PreparedStatement selectService = con.prepareStatement(selectQueryMatiere);
             selectService.setInt(1, matiere);
             ResultSet res = selectService.executeQuery();

             // Ajoute les service une par une a la liste selon les parametres ingresees
             while (res.next()) {

                 //r�cup�re le service
                 Service service = new ServiceJDBC(null, null, null, null);
                 service.load(res.getInt(ID));

                 listeService.add(new ElementListe(service.getId(), service.toString()));


             }
            return listeService;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }
    
    
    public static ArrayList<ElementListe> select(int see) throws ExceptionMetier {
        ArrayList<ElementListe> listeService = new ArrayList<ElementListe>();
        try {
        	 Connection con = ConnectionJDBC.getConnection();
             PreparedStatement selectService = con.prepareStatement(selectQuerySee);
             selectService.setInt(1, see);
             ResultSet res = selectService.executeQuery();

             // Ajoute les service une par une a la liste selon les parametres ingresees
             while (res.next()) {

                 //r�cup�re le service
                 Service service = new ServiceJDBC(null, null, null, null);
                 service.load(res.getInt(ID));

                 listeService.add(new ElementListe(service.getId(), service.toString()));


             }
            return listeService;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }
    
    
    
    public String toString() {
    	return getTypeheure().getNom()
        + " - " + getGroupe().getNom() + " - "
        + getEnseignant().getNom() + "," + getEnseignant().getPrenom();
    }
}
